#include "studentdialoghangkong.h"
#include "ui_studentdialoghangkong.h"
#include <string>
#include "formdo22.h"
#include "my2mainwindow.h"

StudentDialogHangKong::StudentDialogHangKong(QWidget *parent): QDialog(parent), ui(new Ui::StudentDialogHangKong){

    ui->setupUi(this);

    CreateDatabaseFunc();// 调用函数创建且打开数据库
    CreateTableFunc();// 调用函数创建数据表
    onShowUserDb("select * from student");//在构造函数里加载
}

StudentDialogHangKong::~StudentDialogHangKong()
{
    delete ui;
}

void StudentDialogHangKong::CreateDatabaseFunc(){// 创建SQLite数据库 打开连接
    // 1：添加数据库驱动
    sqldb=QSqlDatabase::addDatabase("QSQLITE");

    // 2：设置数据库名称
    sqldb.setDatabaseName("studentmis22.db");

    // 3：打开此数据库是否成功
    if(sqldb.open()==true){
        QMessageBox::information(0,"正确","恭喜你，数据库打开成功！",QMessageBox::Ok);
    }else{
        QMessageBox::critical(0,"错误","数据库打开失败，请重新检测！",QMessageBox::No);
    }
}
void StudentDialogHangKong::CreateTableFunc(){ // 创建SQLite数据表
    QSqlQuery createquery;//执行查询sql语句的类对象

    // 创建SQL语句
    QString strsql=QString("create table student(id int primary key not null , name text not null , sex text not null)");

    // 执行SQL语句
    if(createquery.exec(strsql)==false){
        QMessageBox::critical(0,"失败","数据表创建失败，请重新检查！",QMessageBox::No);
    }else{
        QMessageBox::information(0,"成功","恭喜你，数据表创建成功！",QMessageBox::Ok);
    }
}
void StudentDialogHangKong::QueryTableFunc(){// 执行查询操作

}


void StudentDialogHangKong::on_pushButton_sort_clicked()
{
    QString sort = ui->comboBox_sort->currentText();//排序的字段
    QString order = ui->comboBox_order->currentText();//排序的方式
    QString select_sql = "select * from student";//sql
    if(sort=="姓名"){
        select_sql += " order by name";
    }else if(sort=="学号"){
        select_sql += " order by id";
    }else{
        QMessageBox::critical(this,"错误","提示：请您选择排序内容！",QMessageBox::Ok);
        return;
    }
    if(order=="升序"){
        select_sql += " asc";
    }else if(order=="降序"){
        select_sql += " desc";
    }else{
        QMessageBox::critical(this,"错误","提示：请您选择排序方式！",QMessageBox::Ok);
        return;
    }
    onShowUserDb(select_sql);
}
void StudentDialogHangKong::onShowUserDb(QString select_sql){//数据初始化
    //QString select_sql = "select * from student";//sql
    QTableWidget *tableWidget = ui->tableWidget_student;
    int c = 0, r = 0, nColumn, nRow;
    QSqlQuery sql_query;
    sql_query.prepare(select_sql);
    sql_query.exec();
    sql_query.last();//最后一行
    nRow = sql_query.at() + 1;//行数
    tableWidget->setRowCount(nRow);//设置表行
    nColumn = tableWidget->columnCount();//列
    sql_query.first();//指向第一行
    while(r<nRow){//行
        for (c = 0; c<nColumn; c++){//列
           tableWidget->setItem(r, c, new QTableWidgetItem(sql_query.value(c).toString()));
        }
        r++;
        sql_query.next();
    }
}

void StudentDialogHangKong::on_pushButton_clean_clicked()
{
    ui->comboBox_sort->setCurrentIndex(0);//排序的字段
    ui->comboBox_order->setCurrentIndex(0);//排序的方式
}

void StudentDialogHangKong::on_pushButton_add_clicked(){
    QSqlQuery sqlquery;
    int id = ui->lineEdit_ID -> text().toInt();
    if(id==0){
        QMessageBox::critical(this,"失败","提示：输入错误？学号不能为0？",QMessageBox::Ok);
        return ;
    }

    QString name=ui->lineEdit_name ->text();
    if(name==""){
        QMessageBox::critical(this,"失败","提示：输入错误？姓名不能为空？",QMessageBox::Ok);
        return ;
    }

    QString sex=ui-> comboBox_sex -> currentText();
    if(sex==""){
        QMessageBox::critical(this,"失败","提示：请您选择性别!",QMessageBox::Ok);
        return ;
    }
    // %1  做占位符
    QString strs=QString("insert into student values(%1,'%2','%3')").arg(id).arg(name).arg(sex);

    if(sqlquery.exec(strs)==false){
        QMessageBox::critical(0,"失败","向数据表插入记录失败，请重新检查！",QMessageBox::No);
    }else{
        QMessageBox::information(0,"成功","恭喜你，向数据表插入记录成功！",QMessageBox::Ok);
    }
    onShowUserDb("select * from student");//在构造函数里加载
}

void StudentDialogHangKong::on_pushButton_update_clicked()
{
    QSqlQuery sqlquery;
    int id = ui->lineEdit_ID -> text().toInt();
    if(id<1){
        QMessageBox::critical(this,"失败","提示：请您输入要删除的学号？",QMessageBox::Ok);
        return ;
    }
    QString name=ui->lineEdit_name ->text();
    if(name==""){
        QMessageBox::critical(this,"失败","提示：输入错误？姓名不能为空？",QMessageBox::Ok);
        return ;
    }
    QString sex=ui-> comboBox_sex -> currentText();

    QString strs=QString("update student set name = '%1' , sex = '%2' where id = %3 ").arg(name).arg(sex).arg(id);
    if(sqlquery.exec(strs)==false){
        QMessageBox::critical(0,"失败","修改失败，请您联系管理员！",QMessageBox::Ok);
    }else{
        QMessageBox::information(0,"成功","恭喜你，修改成功！",QMessageBox::Ok);
    }
    onShowUserDb("select * from student");//在构造函数里加载
}

void StudentDialogHangKong::on_pushButton_delete_clicked()
{
    QSqlQuery sqlquery;
    int id = ui->lineEdit_ID -> text().toInt();
    if(id<1){
        QMessageBox::critical(this,"失败","提示：请您输入要删除的学号？",QMessageBox::Ok);
        return ;
    }
    QMessageBox::StandardButton box;
    box = QMessageBox::question(this, "提示", "确实要删除吗?", QMessageBox::Yes|QMessageBox::No);
    if(box==QMessageBox::No)return;

    QString strs=QString("delete from student where id = %1 ").arg(id);
    if(sqlquery.exec(strs)==false){
        QMessageBox::critical(0,"失败","删除失败，请您联系管理员！",QMessageBox::Ok);
    }else{
        QMessageBox::information(0,"成功","恭喜你，删除成功！",QMessageBox::Ok);
    }
    onShowUserDb("select * from student");//在构造函数里加载
}

void StudentDialogHangKong::on_pushButton_search_clicked()
{
    bool hadKey = false;
    QString sql = "select * from student Where 1=1 ";
    int id = ui->lineEdit_ID -> text().toInt();
    if(id>0){
        hadKey = true;
        sql += " And id = " + QString::number(id);
    }
    QString name=ui->lineEdit_name ->text();
    if(name!=""){
        hadKey = true;
        sql += " And name like '%"+name+"%' ";
    }
    QString sex=ui-> comboBox_sex -> currentText();
    if(sex!=""){
        hadKey = true;
        sql += " And sex = '"+sex+"' ";
    }
    if(!hadKey){
        QMessageBox::critical(this,"失败","提示：输入查询条件？",QMessageBox::Ok);
        return ;
    }
    QTableWidget *tableWidget = ui->tableWidget_student;
    tableWidget->clear();
    onShowUserDb(sql);//在构造函数里加载
}


/**
  QSqlQuery
seek(int n) ：query指向结果集的第n条记录；
first() ：query指向结果集的第一条记录；
last() ：query指向结果集的最后一条记录；
next() ：query指向下一条记录，每执行一次该函数，便指向相邻的下一条记录；
previous() ：query指向上一条记录，每执行一次该函数，便指向相邻的上一条记录；
record() ：获得现在指向的记录；
value(int n) ：获得属性的值。其中n表示你查询的第n个属性，比方上面我们使用“select * from student”就相当于“select id, name from student”，那么value(0)返回id属性的值，value(1)返回name属性的值。该函数返回QVariant类型的数据，关于该类型与其他类型的对应关系，可以在帮助中查看QVariant。
at() ：获得现在query指向的记录在结果集中的编号。
  **/


void StudentDialogHangKong::on_pushButton_to2_clicked()
{
    this->close();
    FormDo22 *w=new FormDo22();
    w->show();
}

void StudentDialogHangKong::on_pushButton_to2_win_clicked()
{
    this->close();
    My2MainWindow *w = new My2MainWindow();
    //ui->label_do_in2->setText(w->label_do_in2->test);
    w->show();
}
